import { Provide } from '@midwayjs/decorator';
import { InjectEntityModel } from '@midwayjs/orm';
import { loeeBaseService } from '../../../comservice/loeeBaseService';
import { Repository } from 'typeorm';
import { classgroupStudentsEntity } from '../entity/classgroupStudents';

/**
 * 描述
 */
@Provide()
export class classgroupStudentsService extends loeeBaseService {
  @InjectEntityModel(classgroupStudentsEntity)
  classgroupStudentsEntity: Repository<classgroupStudentsEntity>;

  
  /**
     * 重写list
     * @param query 查询条件
     * @param option 查询配置
     */
   async list(query, option) {
    console.log(option);
    
    const { classgroup_id } = query;
    
    const sql = `
      SELECT
          a.classgroup_id,a.student_id,b.name,c.common_num,c.special_num,d.common_use_num,d.special_use_num,(c.common_num-d.common_use_num) as common_leave_num,(c.special_num-d.special_use_num) as special_leave_num
      FROM
          classgroup_students a
          LEFT JOIN student b on a.student_id = b.id
          LEFT JOIN (
            SELECT 
                student,sum(common_num) as common_num,sum(special_num) as special_num 
            FROM
                finance
            WHERE
                isDelete = false 
            GROUP BY student
          ) c on a.student_id = c.student
          LEFT JOIN (
            SELECT 
                student,sum(common_use_num) as common_use_num,sum(special_use_num) as special_use_num 
            FROM
                student_class_detail
            WHERE
                isDelete = false and isLeave = false
            GROUP BY student
          ) d on a.student_id = d.student

      WHERE 1 = 1 and a.isDelete = false and b.isDelete = false
      ${this.setSql(classgroup_id, 'and a.classgroup_id = ?', [classgroup_id])}
      GROUP BY a.id
      `;
    
    return this.sqlRenderList(sql, query);
  }

  async sqlRenderList(sql, query) {
    
    let params = [];
    params = params.concat(this.sqlParams);
    const result = await this.nativeQuery(sql, params);
    return result
}

}
